const express = require('express');
const povertyAchievementRouter = express.Router();
const { sendResult } = require('../utils/index')

const mysql = require('mysql')
const db = mysql.createPool({
    host: "127.0.0.1",
    user: "root",
    password: "admin123",
    database: "mydatabase_01",
    port: 3306,
})


const table = {
    name: 'peoplechange',
}

povertyAchievementRouter.use(express.json());
povertyAchievementRouter.use(express.urlencoded({ extended: true }));

//贫困人数变化
povertyAchievementRouter.get('/poverty-product/list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${table.name}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }
        })
    }
})

//新增 修改
povertyAchievementRouter.post('/poverty-product/edit', (req, res) => {
    let { id } = req.query
    let { year, newCount, oldCount, back, incidenceRate, team, povertyIncome, provinceIncome, centre, municipality, bannerCounty, mixMoney, otherMoney } = req.body
    console.log(req.body);

    if (id) {
        if (year && newCount && oldCount && back && incidenceRate && team && povertyIncome && provinceIncome && centre && municipality && bannerCounty && mixMoney && otherMoney) {
            let sql = `UPDATE ${table.name} SET year = '${year}',newCount = '${newCount}',oldCount = '${oldCount}', back = '${back}', incidenceRate='${incidenceRate}',team ='${team}',povertyIncome='${povertyIncome}',provinceIncome='${provinceIncome}' , centre='${centre}' , municipality=${municipality} ,bannerCounty =${bannerCounty} ,mixMoney=${mixMoney} ,otherMoney=${otherMoney}   WHERE id = ${id}`
            db.query(sql, (err, result) => {
                if (err) {
                    res.status(500).send(err.response)
                } else {
                    sendResult(res, {
                        msg: '修改成功'
                    })
                }
            }
            )
        } else {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        }
    } else {
        if (!year || !newCount || !oldCount || !back || !incidenceRate || !team || !povertyIncome || !provinceIncome || !centre || !municipality || !bannerCounty || !mixMoney || !otherMoney) {

            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)

        } else {
            let sql = `INSERT INTO ${table.name} (year,  newCount, oldCount, back, incidenceRate,team,povertyIncome,provinceIncome,centre,municipality,bannerCounty,mixMoney,otherMoney) VALUES ('${year}', '${newCount}', '${oldCount}', '${back}' ,'${incidenceRate}', '${team}', '${povertyIncome}', '${provinceIncome}', '${centre}', '${municipality}', '${bannerCounty}', '${mixMoney}', '${otherMoney}')`
            db.query(sql, (err, result) => {
                if (err) {
                    console.log(err);
                    if (err.errno === 1062) {
                        sendResult(res, {
                            msg: '该数据已存在'
                        }, 201)
                    } else {
                        res.status(500).send(err)
                    }

                } else {
                    sendResult(res, {
                        msg: '新增成功！'
                    })
                }
            })
        }

    }

})

//删除
povertyAchievementRouter.get('/poverty-product/del', (req, res) => {
    let { id } = req.query
    db.query(`DELETE FROM ${table.name} WHERE id = ${id}`, (err, result) => {
        if (err) {
            res.status(500).send(err)
        } else {
            sendResult(res, {
                msg: '删除成功'
            })
        }
    })
})

//获取每年的拨款信息
povertyAchievementRouter.get('/poverty-product/money-list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} `
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err); return
        }
        else {
            let moneyObj = {
                centre: [],
                municipality: [],
                bannerCounty: [],
                mixMoney: [],
                otherMoney: []
            }
            result.forEach(item => {
                moneyObj.centre.push(item.centre)
                moneyObj.municipality.push(item.municipality)
                moneyObj.bannerCounty.push(item.bannerCounty)
                moneyObj.mixMoney.push(item.mixMoney)
                moneyObj.otherMoney.push(item.otherMoney)
            })
            sendResult(res, moneyObj)
            return
        }
    })
})

module.exports = povertyAchievementRouter